package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import entity.Student;

import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;

import utils.JDBCUtil;

//stuDao的实现类
public class StudentDaoJTableImpl implements StudentDao {

	private JTable jTable;

	public StudentDaoJTableImpl(JTable jTable) {
		this.jTable = jTable;
	}

	// 添加一行学生信息于表格上
	@Override
	public boolean save(Student student) {
		DefaultTableModel model = (DefaultTableModel) jTable.getModel();
		String[] strs = convertStudentToStringArr(student);
		model.addRow(strs);
		return true;
	}

	public String[] convertStudentToStringArr(Student student) {
		String[] strs = new String[8];
		strs[0] = student.getStuNo();
		strs[1] = student.getStuName();
		strs[2] = student.getGender();
		strs[3] = student.getBirthDate();
		strs[4] = student.getPoliticalStatus();
		strs[5] = student.getHomeAddress();
		strs[6] = student.getTelephone();
		strs[7] = student.getDormitoryNumber();
		return strs;
	}

	// 移除表格上指定行
	@Override
	public boolean deleteByIndex(int index) {
		DefaultTableModel model = (DefaultTableModel) jTable.getModel();
		model.removeRow(index);
		return true;
	}

	// 一行学生信息存入数据库
	@Override
	public boolean saveInDB(Student student) {
		Connection conn = null;
		PreparedStatement pstat = null;
		String sql = "insert into student(num,name,gender,birthDate,status,address,phoneNum,dormNum)values(?,?,?,?,?,?,?,?) ";
		int result = -1;
		try {
			conn = JDBCUtil.getConnection();
			pstat = (PreparedStatement) conn.prepareStatement(sql);
			pstat.setString(1, student.getStuNo());
			pstat.setString(2, student.getStuName());
			pstat.setString(3, student.getGender());
			pstat.setString(4, student.getBirthDate());
			pstat.setString(5, student.getPoliticalStatus());
			pstat.setString(6, student.getHomeAddress());
			pstat.setString(7, student.getTelephone());
			pstat.setString(8, student.getDormitoryNumber());
			result = pstat.executeUpdate();
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.realeaseAll(null, pstat, conn);
		}

		return true;
	}

	// 根据查询关键字获取数据库中相匹配学生信息
	@Override
	public ArrayList<Student> searchStudents(String searchKey,
			String searchValue) {
		// TODO Auto-generated method stub
		ArrayList<Student> stulist = new ArrayList<Student>();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		String sql = "select * from student where " + searchKey + " like ?";// 表中有id和name这列
		try {
			conn = JDBCUtil.getConnection();
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, searchValue);
			rs = pstat.executeQuery();
			while (rs.next()) {
				String stuNo = rs.getString("num");
				String stuName = rs.getString("name");
				String gender = rs.getString("gender");
				String birthDate = rs.getString("birthDate");
				String politicalStatus = rs.getString("status");
				String homeAddress = rs.getString("address");
				String telephone = rs.getString("phoneNum");
				String dormitoryNumber = rs.getString("dormNum");
				Student student = new Student(stuNo, stuName, gender,
						birthDate, politicalStatus, homeAddress, telephone,
						dormitoryNumber);
				stulist.add(student);
			}
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.realeaseAll(rs, pstat, conn);
		}
		return stulist;
	}

	// 根据学号删除数据库中对应信息
	@Override
	public boolean deleteStudent(String stuNo) {
		Connection conn = null;
		PreparedStatement pstat = null;
		int result = 0;
		String sql = "delete from student where num like ?";
		try {
			conn = JDBCUtil.getConnection();
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, stuNo);
			result = pstat.executeUpdate();
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.realeaseAll(null, pstat, conn);
		}
		if (result != 0) {
			return true;
		} else {
			return false;
		}
	}

	// 获取数据库中存在的学生信息
	@Override
	public ArrayList<Student> getAllStudent() {
		// TODO Auto-generated method stub
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		String sql = "select * from student";
		ArrayList<Student> stulist = new ArrayList<Student>();
		try {
			conn = JDBCUtil.getConnection();
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			while (rs.next()) {
				String stuNo = rs.getString("num");
				String stuName = rs.getString("name");
				String gender = rs.getString("gender");
				String birthDate = rs.getString("birthDate");
				String politicalStatus = rs.getString("status");
				String homeAddress = rs.getString("address");
				String telephone = rs.getString("phoneNum");
				String dormitoryNumber = rs.getString("dormNum");
				Student student = new Student(stuNo, stuName, gender,
						birthDate, politicalStatus, homeAddress, telephone,
						dormitoryNumber);
				stulist.add(student);
			}
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.realeaseAll(rs, stat, conn);
		}
		return stulist;

	}

	@Override
	public Student modifyFindStudent(String stuNo) {
		// 找到数据库中相同学号的一行信息
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		String sql = "select * from student where num = ?";
		Student student = null;
		try {
			conn = JDBCUtil.getConnection();
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, stuNo);
			rs = pstat.executeQuery();
			while (rs.next()) {
				String stuNo1 = rs.getString("num");
				String stuName = rs.getString("name");
				String gender = rs.getString("gender");
				String birthDate = rs.getString("birthDate");
				String politicalStatus = rs.getString("status");
				String homeAddress = rs.getString("address");
				String telephone = rs.getString("phoneNum");
				String dormitoryNumber = rs.getString("dormNum");
				student = new Student(stuNo1, stuName, gender, birthDate,
						politicalStatus, homeAddress, telephone,
						dormitoryNumber);
			}
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.realeaseAll(rs, pstat, conn);
		}
		return student;
	}

	// 将修改后的信息对应存在数据库中的指定行
	public boolean modifySaveInDB(Student student, String stuNo) {
		Connection conn = null;
		PreparedStatement pstat = null;
		String sql = "update student set name=?,gender=?,birthDate=?,status=?,address=?,phoneNum=?,dormNum=? where num = ?";
		int result = 0;
		try {
			conn = JDBCUtil.getConnection();
			pstat = (PreparedStatement) conn.prepareStatement(sql);
			pstat.setString(1, student.getStuName());
			pstat.setString(2, student.getGender());
			pstat.setString(3, student.getBirthDate());
			pstat.setString(4, student.getPoliticalStatus());
			pstat.setString(5, student.getHomeAddress());
			pstat.setString(6, student.getTelephone());
			pstat.setString(7, student.getDormitoryNumber());
			pstat.setString(8, stuNo);
			result = pstat.executeUpdate();
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.realeaseAll(null, pstat, conn);
		}
		if (result != 0) {
			return true;
		} else {
			return false;
		}
	}

	// 修改表格上数据
	public boolean modifyInTable(int index, String[] content, String num) {
		DefaultTableModel model = (DefaultTableModel) jTable.getModel();
		model.setValueAt(num, index, 0);
		for (int i = 1; i < 8; i++) {
			model.setValueAt(content[i], index, i);
		}
		return true;
	}

}
